package com.CommunityHub.kiana.dao;

import com.CommunityHub.kiana.po.VenueInfo;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class VenueInfoDAO {

    // 根据筛选条件查询场馆
    public List<VenueInfo> findByFilters(String venueType, Integer usageStatus, String location) throws SQLException {
        System.out.println("===== 进入 findByFilters 方法 =====");
        System.out.println("筛选条件 - 场馆类型: " + venueType + ", 使用状态: " + usageStatus + ", 位置: " + location);

        StringBuilder sql = new StringBuilder("SELECT * FROM venue_info WHERE publish_status = 1"); // 只查已发布
        List<Object> params = new ArrayList<>();

        // 拼接条件
        if (venueType != null && !venueType.isEmpty()) {
            sql.append(" AND venue_type = ?");
            params.add(venueType);
            System.out.println("添加场馆类型筛选条件: " + venueType);
        }
        if (usageStatus != null) {
            sql.append(" AND usage_status = ?");
            params.add(usageStatus);
            System.out.println("添加使用状态筛选条件: " + usageStatus);
        }
        if (location != null && !location.isEmpty()) {
            sql.append(" AND location = ?");
            params.add(location);
            System.out.println("添加位置筛选条件: " + location);
        }

        System.out.println("生成的SQL: " + sql.toString());
        List<VenueInfo> venues = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            System.out.println("数据库连接获取成功: " + conn);

            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
                System.out.println("设置参数 " + (i+1) + ": " + params.get(i));
            }

            rs = ps.executeQuery();
            System.out.println("查询执行成功，开始处理结果集");

            while (rs.next()) {
                VenueInfo venue = mapVenue(rs);
                venues.add(venue);
                System.out.println("找到场馆: " + venue.getVenueName() + " (ID: " + venue.getVenueId() + ")");
            }
            System.out.println("查询完成，共找到 " + venues.size() + " 个场馆");
        } catch (SQLException e) {
            System.err.println("查询失败: " + e.getMessage());
            throw e;
        } finally {
            DBUtil.close(conn, ps, rs);
            System.out.println("资源已关闭");
        }
        return venues;
    }

    // 根据ID查询场馆详情
    public VenueInfo findById(Integer venueId) throws SQLException {
        System.out.println("===== 进入 findById 方法 =====");
        System.out.println("查询场馆ID: " + venueId);

        String sql = "SELECT * FROM venue_info WHERE venue_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            System.out.println("数据库连接获取成功: " + conn);

            ps = conn.prepareStatement(sql);
            ps.setInt(1, venueId);
            System.out.println("设置查询参数: venue_id = " + venueId);

            rs = ps.executeQuery();
            System.out.println("查询执行成功");

            if (rs.next()) {
                VenueInfo venue = mapVenue(rs);
                System.out.println("找到场馆: " + venue.getVenueName() + " (ID: " + venue.getVenueId() + ")");
                return venue;
            }
            System.out.println("未找到ID为 " + venueId + " 的场馆");
            return null;
        } catch (SQLException e) {
            System.err.println("查询失败: " + e.getMessage());
            throw e;
        } finally {
            DBUtil.close(conn, ps, rs);
            System.out.println("资源已关闭");
        }
    }

    // 更新场馆状态（可用/维护中）
    public boolean updateStatus(Integer venueId, Integer newStatus) throws SQLException {
        System.out.println("===== 进入 updateStatus 方法 =====");
        System.out.println("更新场馆ID: " + venueId + " 的状态为: " + newStatus);

        String sql = "UPDATE venue_info SET usage_status = ? WHERE venue_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            System.out.println("数据库连接获取成功: " + conn);

            ps = conn.prepareStatement(sql);
            ps.setInt(1, newStatus);
            ps.setInt(2, venueId);
            System.out.println("设置更新参数: usage_status = " + newStatus + ", venue_id = " + venueId);

            int rows = ps.executeUpdate();
            System.out.println("更新执行成功，影响行数: " + rows);
            return rows > 0;
        } catch (SQLException e) {
            System.err.println("状态更新失败: " + e.getMessage());
            throw e;
        } finally {
            DBUtil.close(conn, ps, null);
            System.out.println("资源已关闭");
        }
    }

    // 映射ResultSet到实体类
    private VenueInfo mapVenue(ResultSet rs) throws SQLException {
        VenueInfo venue = new VenueInfo();
        venue.setVenueId(rs.getInt("venue_id"));
        venue.setVenueName(rs.getString("venue_name"));
        venue.setAddress(rs.getString("address"));
        venue.setSpecialNote(rs.getString("special_note"));
        venue.setOpeningTime(rs.getTime("opening_time"));
        venue.setClosingTime(rs.getTime("closing_time"));
        venue.setContactPhone(rs.getString("contact_phone"));
        venue.setUsageStatus(rs.getInt("usage_status"));
        venue.setLocation(rs.getString("location"));
        venue.setSize(rs.getBigDecimal("size"));
        venue.setVenueType(rs.getString("venue_type"));
        venue.setResponsibleId(rs.getInt("responsible_id"));
        venue.setChargeStandard(rs.getBigDecimal("charge_standard"));
        venue.setPublishStatus(rs.getInt("publish_status"));

        System.out.println("映射结果: 场馆名称 = " + venue.getVenueName() + ", ID = " + venue.getVenueId());
        return venue;
    }

    // 更新场馆信息
    public void update(VenueInfo venueInfo) throws SQLException {
        System.out.println("===== 进入 update 方法 =====");
        System.out.println("更新场馆ID: " + venueInfo.getVenueId() + ", 名称: " + venueInfo.getVenueName());

        String sql = "UPDATE venue_info SET " +
                "venue_name = ?, " +
                "venue_type = ?, " +
                "usage_status = ?, " +
                "address = ?, " +
                "location = ?, " +
                "opening_time = ?, " +
                "closing_time = ?, " +
                "responsible_id = ?, " +
                "contact_phone = ?, " +
                "size = ?, " +
                "special_note = ?, " +
                "charge_standard = ? " +
                "WHERE venue_id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            System.out.println("数据库连接获取成功: " + conn);

            pstmt = conn.prepareStatement(sql);

            // 打印所有参数（调试用）
            System.out.println("更新参数列表:");
            System.out.println("1. venue_name = " + venueInfo.getVenueName());
            System.out.println("2. venue_type = " + venueInfo.getVenueType());
            System.out.println("3. usage_status = " + venueInfo.getUsageStatus());
            System.out.println("4. address = " + venueInfo.getAddress());
            System.out.println("5. location = " + venueInfo.getLocation());
            System.out.println("6. opening_time = " + venueInfo.getOpeningTime());
            System.out.println("7. closing_time = " + venueInfo.getClosingTime());
            System.out.println("8. responsible_id = " + venueInfo.getResponsibleId());
            System.out.println("9. contact_phone = " + venueInfo.getContactPhone());
            System.out.println("10. size = " + venueInfo.getSize());
            System.out.println("11. special_note = " + venueInfo.getSpecialNote());
            System.out.println("12. charge_standard = " + venueInfo.getChargeStandard());
            System.out.println("13. venue_id = " + venueInfo.getVenueId());

            // 设置参数
            pstmt.setString(1, venueInfo.getVenueName());
            pstmt.setString(2, venueInfo.getVenueType());
            pstmt.setInt(3, venueInfo.getUsageStatus());
            pstmt.setString(4, venueInfo.getAddress());
            pstmt.setString(5, venueInfo.getLocation());
            pstmt.setTime(6, Time.valueOf(venueInfo.getOpeningTime().toString()));  // 修正时间参数设置
            pstmt.setTime(7, Time.valueOf(venueInfo.getClosingTime().toString()));  // 修正时间参数设置
            pstmt.setInt(8, venueInfo.getResponsibleId());  // 修正为int类型
            pstmt.setString(9, venueInfo.getContactPhone());
            pstmt.setBigDecimal(10, venueInfo.getSize());
            pstmt.setString(11, venueInfo.getSpecialNote());
            pstmt.setBigDecimal(12, venueInfo.getChargeStandard());
            pstmt.setInt(13, venueInfo.getVenueId());

            // 执行更新
            int rows = pstmt.executeUpdate();
            System.out.println("更新执行成功，影响行数: " + rows);

            if (rows == 0) {
                System.err.println("更新失败：未找到ID为 " + venueInfo.getVenueId() + " 的场馆");
                throw new SQLException("更新失败：未找到对应场馆ID的记录");
            }
            System.out.println("更新成功");
        } catch (SQLException e) {
            System.err.println("更新失败: " + e.getMessage());
            throw e;
        } finally {
            DBUtil.close(conn, pstmt, null);
            System.out.println("资源已关闭");
        }
    }

    public void addVenue(VenueInfo newVenue) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        // SQL插入语句，根据您的表结构可能需要调整
        String sql = "INSERT INTO venue_info " +
                "(venue_name, venue_type, usage_status, address, location, " +
                "opening_time, closing_time, responsible_id, contact_phone, " +
                "size, special_note, charge_standard) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try {
            // 获取数据库连接（根据您的实际情况调整）
            conn = DBUtil.getConnection(); // 假设您有一个DBUtils类获取连接

            // 预编译SQL语句
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            // 设置参数
            stmt.setString(1, newVenue.getVenueName());
            stmt.setString(2, newVenue.getVenueType());
            stmt.setInt(3, newVenue.getUsageStatus());
            stmt.setString(4, newVenue.getAddress());
            stmt.setString(5, newVenue.getLocation());
            stmt.setString(6, String.valueOf(newVenue.getOpeningTime()));
            stmt.setString(7, String.valueOf(newVenue.getClosingTime()));
            stmt.setString(8, String.valueOf(newVenue.getResponsibleId()));
            stmt.setString(9, newVenue.getContactPhone());
            stmt.setBigDecimal(10, newVenue.getSize());
            stmt.setString(11, newVenue.getSpecialNote());
            stmt.setBigDecimal(12, newVenue.getChargeStandard());

            // 执行插入
            int rowsAffected = stmt.executeUpdate();

            if (rowsAffected > 0) {
                // 获取自动生成的主键
                rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    int generatedId = rs.getInt(1);
                    newVenue.setVenueId(generatedId); // 设置生成的ID到对象
                }
                System.out.println("场馆添加成功，ID: " + newVenue.getVenueId());
            } else {
                throw new SQLException("场馆添加失败，没有记录被插入");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("数据库操作错误: " + e.getMessage(), e);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("添加场馆失败: " + e.getMessage(), e);
        } finally {
            // 关闭资源
            DBUtil.close(conn,stmt, rs); // 假设您有一个DBUtils类关闭资源
        }
    }

    public boolean deleteVenue(int venueId) {
       /* System.out.println("===== 进入 deleteVenue 方法 =====");
        System.out.println("删除场馆ID: " + venueId);*/

        String sql = "DELETE FROM venue_info WHERE venue_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            /*System.out.println("数据库连接获取成功: " + conn);*/

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, venueId);
            /*System.out.println("设置删除参数: venue_id = " + venueId);*/

            int rows = pstmt.executeUpdate();
            /*System.out.println("删除执行成功，影响行数: " + rows);*/
            return rows > 0;
        } catch (SQLException e) {
           /* System.err.println("删除失败: " + e.getMessage());*/
            throw new RuntimeException("删除场馆失败: " + e.getMessage(), e);
        } finally {
            DBUtil.close(conn, pstmt, null);
            /*System.out.println("资源已关闭");*/
        }
    }
}